import fs from 'fs';
import { connection } from '../app/database/mysql';
import { swiperModel } from './swiper.model';
import { APP_URL, STATIC_PATH } from '../app/app.config';

/**
 * 获取内容
 */
export const getSwiper = async (pageNo: number, pageSize: number, status: number, category: number, type: number) => {
  // 准备查询
  let statement = `
    SELECT SQL_CALC_FOUND_ROWS *, COUNT(id) OVER () AS total FROM swiper
  `;
  let where = []
  if (status >= 0) {
    where.push(`status = ` + status)
  }
  if (category >= 0) {
    where.push(`category = ` + category)
  }
  if (type >= 0) {
    where.push(`type = ` + type)
  }
  if (where.length > 0) {
    statement += ` where `
    for (let i = 0; i < where.length; i++) {
      statement += where[i]
      if (i < where.length - 1) {
        statement += ` and `
      }
    }
  }
  statement += ` ORDER by id DESC, id DESC limit ` + (pageNo > 0 ? (pageNo - 1) * pageSize : 0) + `,` + pageSize

  // 执行查询
  const [data] = await connection.promise().query(statement);

  let totalCount: any;
  if (data[0] && data[0].total) totalCount = data[0].total;
  let totalPage = (totalCount % pageSize) > 0 ? parseInt(`${totalCount / pageSize}`) + 1 : totalCount / pageSize
  
  for(let item of (data as any)) {
    let movieName = await MovieName(item.content_id);
    item.movieName = movieName[0].name;
    item.movieName_zh = movieName[0].name_zh;
  }

  // 提供数据
  let res = {
    data,
    totalPage,
    totalCount
  }
  return res;
}

/**
 * 获取电影名
 */
const MovieName = async (movieId: number) => {
  // 准备查询
  const statement = `
    SELECT * FROM movies
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, movieId);

  // 提供数据
  return data;
}

/**
 * 创建内容
 */
export const createSwiper = async (post: swiperModel) => {
  // 准备查询
  const statement = `
    INSERT INTO swiper
    SET ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, post);

  // 提供数据
  return data;
}

/**
 * 按 ID 获取内容
 */
export const getIdSwiper = async (postId: number) => {
  // 准备查询
  const statement = `
    SELECT * FROM swiper
    WHERE category = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, postId);

  for (let item of (data as any)) {
    let movieName = await MovieName(item.content_id);
    item.movieName = movieName[0].name;
    item.movieName_zh = movieName[0].name_zh;
  }
  
  // 提供数据
  return data;
}

/**
 * 更新内容
 */
export const updateSwiper = async (post: swiperModel, postId: number) => {
  // 准备查询
  const statement = `
    UPDATE swiper
    SET ?
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, [post, postId]);

  // 提供数据
  return data;
};

/**
 * 删除内容
 */
export const deleteSwiper = async (postId: number) => {
  // 准备查询
  const statement = `
    DELETE FROM swiper
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, postId);

  // 提供数据
  return data;
}

/**
 * 存储文件信息
 */
export const createFile = async (
  file: swiperModel
) => {
  // 准备查询
  const statement = `
    INSERT INTO resource
    SET ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, file);

  // 提供数据
  return data;
}

/**
 * 更新文件信息
 */
export const updateFile = async (post: swiperModel, postId: number) => {
  const statement = `
    UPDATE resource
    SET ?
    WHERE id = ?
  `;

  // 执行查询
  const [data] = await connection.promise().query(statement, [post, postId]);

  // 提供数据
  return data;
}

/**
 * 按 ID 查找文件
 */
export const findFileById = async (fileId: number, targetId: number, type: number) => {
  // 准备查询
  let sql = 'SELECT * FROM resource ';
  let and = false;
  if (fileId > 0) {
    sql += " where id = " + fileId;
    and = true;
  }
  if (targetId > 0) {
    sql += (and ? " and " : " where ") + " target_id = " + targetId;
    and = true;
  }
  if (type > 0) {
    sql += (and ? " and " : " where ") + " target_type = " + type;
  }

  // 执行查询
  const [data] = await connection.promise().query(sql);
  for (let item of data as any) {
    item.fullUrl = APP_URL + item.url + item.filename
  }
  // 提供数据
  return data as any;
}

/**
 * 删除内容文件
 */
export const deleteFileById = async (file: swiperModel) => {
  if (!file.id || file.id <= 0) return
  const statement = `delete from resource where id = ?`;
  const [data] = await connection.promise().query(statement, file.id);
  if ((data as any).affectedRows > 0) {
    let path = STATIC_PATH + file.url + file.filename

    try {
      fs.unlinkSync(path);
    } catch (error) {
      console.log("fileDeleteErr:", error);
    }
  };

}
